MySQL 事件

概述

事件调度器是 MySQL5.1 后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为时间触发器，类似于 Linux 系统下面的任务调度器 crontab，或者类似与 Windows 下面的计划任务。 MySQL 的事件调度器可以精确到每秒钟执行一个任务。

开启事件功能

查看事件功能是否开启：

show variables like '%event_scheduler%';

显示 event_scheduler 为 OFF 时，可以使用以下命令开启：

set global event_scheduler=1;

但是在 MySQL 重启之后，将会恢复原来状态，要想数据库重启后也可以让 event_scheduler 开启，则需要修改配置文件 my.cnf ：

[mysqld]
event_scheduler=ON //这一行加入mysqld标签下

创建计划任务

1. 查看所有计划任务

SELECT * FROM information_schema.`EVENTS`;

2.创建计划任务

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;
schedule:
    AT timestamp [+ INTERVAL interval] ...
     | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
interval:
  quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
            WEEK | SECOND | YEAR_MONTH | DAY_HOUR |
            DAY_MINUTE |DAY_SECOND | HOUR_MINUTE |
            HOUR_SECOND | MINUTE_SECOND}

• DEFINER： 定义事件执行的时候检查权限的用户。
• ON SCHEDULE： 定义执行的时间和时间间隔。
• ON COMPLETION [NOT] PRESERVE：定义事件是一次执行还是永久执行，默认为一次执行，即 NOT PRESERVE。
• ENABLE | DISABLE | DISABLE ON SLAVE：定义事件创建以后是开启还是关闭，以及在从上关闭。如果是从服务器自动同步主上的创建事件的语句的话，会自动加上 DISABLE ON SLAVE。
• `COMMENT comment'：定义事件的注释。

举例：
1）创建一个10分钟后清空 test 表数据的事件

CREATE EVENT IF NOT EXISTS event_truncate_test2
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test2;

2）创建每秒钟执行一次 test_proce 存储过程的事件

CREATE EVENT IF NOT EXISTS `event_test`
ON SCHEDULE EVERY 1 SECOND
ON COMPLETION PRESERVE DISABLE
DO CALL test_proce();

3. 常见周期定时规则

1）周期执行—— EVERY，单位有：second，minute，hour，day，week，quarter，month，year，如：

on schedule every 1 second //每秒执行1次
on schedule every 2 minute //每两分钟执行1次
on schedule every 3 day //每3天执行1次

2）在具体某个时间执行—— AT，如：

on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at current_timestamp()+interval 10 minute //10分钟后执行
on schedule at '2016-10-01 21:50:00' //在2016年10月1日，晚上9点50执行

3）在某个时间段执行—— STARTS ENDS，如：

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天

4. 更改事件

更改事件的语法如下：

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO event_body]

例如：

ALERT EVENT event_name ON COMPLETION PRESERVE ENABLE; //开启定时任务
ALERT EVENT event_name ON COMPLETION PRESERVE DISABLE; //关闭定时任务

5. 删除事件

DROP EVENT [IF EXISTS] event_name;

事件的优缺点及应用场景

1. 优点

• 定时任务由dba统一管理，避免部署在操作系统层
• 减少系统管理员产生误操作的风险
• 有利于后续的管理和维护

2. 缺点

• 在繁忙且要求性能的数据库上慎重部署和启用调度器
• 过于复杂的处理更适合使用程序实现
• 开启和关闭事件需要具有超级用户权限。

3. 应用场景

适用于定期收集统计信息，定期清除历史数据，定期数据库检查等等。

参考文章：

1. mysql中的事件